********************************************************************************
*
*    CLEANING THE MENTOR REPORT DATABASE AND CACULATING TAKE-UP RATE 
*
********************************************************************************
* open the report survey completed by mentors 
import delimited using "$raw/report/20220404175801-SurveyExport.csv", clear 
keep if ~missing(thisistheendofthereportifyouwoul)
rename whatisyourmentorid mentorid
rename whatistheidofthementeeyoumeet menteeid
rename whatisthenameofthementeeyoumeet menteename
rename whatisyourname mentorname

replace menteeid = strlower(menteeid)
replace menteeid = subinstr(menteeid, " ", "",.)
replace mentorid = subinstr(mentorid, " ", "",.)
replace mentorid = subinstr(mentorid, ".", "",.)
replace menteeid = subinstr(menteeid, "o", "0",.)
replace menteeid = subinstr(menteeid, "mscbr", "mscnr",.)
replace menteeid = subinstr(menteeid, "msgi", "msgci",.)
replace menteeid = subinstr(menteeid, "msgc1", "msgci",.)
replace menteeid = subinstr(menteeid, "msg1", "msgci",.)
replace menteeid = subinstr(menteeid, "mttp.", "mttp",.)
replace mentorid = subinstr(mentorid, "MSGC1", "MSGCI",.)
replace mentorid = subinstr(mentorid, "MSGC0", "MSGCI0",.)
replace mentorid = subinstr(mentorid, "MTKSO", "MTKS0",.)
replace mentorid = subinstr(mentorid, "MSG1", "MSGCI",.)
replace mentorid = subinstr(mentorid, "MSGI", "MSGCI",.)
replace mentorid = subinstr(mentorid, "MGCI", "MSGCI",.)
replace mentorid = subinstr(mentorid, "MSGCl", "MSGCI",.)
replace mentorid = subinstr(mentorid, "MSGCL", "MSGCI",.)
replace mentorid = subinstr(mentorid, "MAJNE", "MSJNE",.)
replace mentorid = subinstr(mentorid, "MDJNE", "MSJNE",.)
replace mentorid = subinstr(mentorid, "Msjne", "MSJNE",.)
replace mentorid = subinstr(mentorid, "MSRTCO2", "MSRTC02",.)
replace menteeid = subinstr(menteeid, "msgcl", "msgci",.)
replace menteeid = subinstr(menteeid, "msgci10", "msgci110",.) if mentorid == "MSGCI11"
replace menteeid = subinstr(menteeid, "msjne40", "msjne040",.) if mentorid == "MSJNE04"
replace menteeid = subinstr(menteeid, "msrtc20", "msrtc020",.) if mentorid == "MSRTC02"

replace mentorid = "MKTKS55" if mentorid == "KTKS55"
replace menteename = "Bisal Subba" if menteeid == "mscst2501"
replace mentorname = "Aita Bahadur Subba" if mentorid == "MSCST25"
replace menteeid = "mtks0604" if menteeid == "mtks0504"
replace menteename = "Kinley Pelmo" if menteeid == "mtks4704"
replace menteename = "Jigdrel Wangchuck" if menteeid == "mttp3602"
replace menteename = "Kinga Om" if menteeid == "mttp3702"
replace menteename = "Hemraj Ghalley" if menteeid == "mttp0301"
replace menteeid = "mtks0102" if menteeid == "mtks01102"	
replace menteename = "Elena Tamang" if menteeid == "mtks0102"
replace menteeid = "msgci3902" if menteeid == "msgci392"
replace menteename = "Lungten Chojay" if menteeid == "msgci3902"
replace menteeid = "mscst2303" if menteename == "Milan Rai" & menteeid == "mscst2302"
replace menteeid = "mscst2302" if menteename == "Krishna Maya Chetri" & menteeid == "mscst2303"
replace menteeid = "mscst2304" if menteename == "Leki Pem" & menteeid == "mscst2303"
replace menteeid = "mtty0805" if (menteename == "Tshawang Tenzin" | menteename == "Tshewang Tenzin")  & mentorid == "MTTY08"
replace menteeid = "msshr0805" if menteename == "Sonam Lhegden Lhendup" & mentorid == "MSSHR08"
replace menteeid = "msgci4401" if menteeid == "msgci1401" & mentorid == "MSGCI44"
replace menteeid = "msgci4402" if menteeid == "msgci1402" & mentorid == "MSGCI44"
replace menteeid = "msgci2604" if menteeid == "msgci204" & mentorid == "MSGCI26"
replace menteeid = "msgci2605" if menteeid == "msgci205" & mentorid == "MSGCI26"
replace menteeid = "msgci2902" if menteeid == "msgci2901" & menteename == "Chimi Yuden"
replace menteeid = "msgci2903" if menteeid == "msgci2901" & menteename == "Dorji Dolma"
replace menteeid = "msgci2904" if menteeid == "msgci2901" & menteename == "Sherab Wangchuk"
replace menteeid = "msgci2905" if menteeid == "msgci2901" & menteename == "Tshering Dema"
replace menteeid = "msgci0502" if menteeid == "msgci502"
replace menteeid = "msjne3102" if menteeid == "msjne302" & mentorid == "MSJNE31"
replace menteeid = "msjne3201" if menteeid == "msjne3021" & mentorid == "MSJNE32"
replace menteeid = "msrtc0301" if menteeid == "msrtc031" & mentorid == "MSRTC03"
replace mentorid = "MSRTC02" if mentorid == "MRSTC02"
replace menteeid = "msrtc0205" if menteeid == "msrtc205"
replace mentorid = "MSSHR41" if mentorid == "MSSH41"
replace menteeid = subinstr(menteeid, "mssh41", "msshr41",.) if mentorid == "MSSHR41"
replace mentorid = "MSSHR29" if mentorid == "MSSH29"
replace menteeid = subinstr(menteeid, "msshr21", "msshr29",.) if mentorid == "MSSHR29"
replace menteeid = subinstr(menteeid, "msshr40", "msshr040",.) if mentorid == "MSSHR04"
replace menteeid = subinstr(menteeid, "msshr40", "msshr420",.) if mentorid == "MSSHR42"
replace menteeid = "msshr1504" if menteeid == "msshr504"
replace mentorid = "MSSHR32" if mentorid == "MSSR32"
replace mentorid = subinstr(mentorid, "Mtks", "MTKS",.)
replace mentorid = subinstr(mentorid, "Mkts", "MTKS",.) 
replace mentorid = subinstr(mentorid, "MTkS", "MTKS",.) 
replace mentorid = subinstr(mentorid, "MKTS", "MTKS",.) 
replace mentorid = "MTKS41" if (mentorid == "MKS4101" | mentorid == "MKS4102")
replace mentorid = "MTKS55" if mentorid == "MKTKS55"
replace menteeid = "mtks0905" if menteeid == "mtsk0905"
replace menteeid = "mtks6102" if menteeid == "mtks6104" & menteename == "Karma Yangzom"
replace menteeid = "mtks6104" if menteeid == "mtks6103" & menteename == "Tshewang Tenzin Dorji"
replace mentorid = subinstr(mentorid, "Mttp", "MTTP",.) 
replace mentorid = subinstr(mentorid, "Mtpp", "MTTP",.) 
replace mentorid = subinstr(mentorid, "MMTP", "MTTP",.) 
replace mentorid = subinstr(mentorid, "MTTp", "MTTP",.)
replace mentorid = subinstr(mentorid, "Mttb", "MTTP",.)
replace mentorid = subinstr(mentorid, "MTTB", "MTTP",.)
replace mentorid = subinstr(mentorid, "MSsHR", "MSSHR",.)
replace mentorid = subinstr(mentorid, "MYKS", "MTKS",.)
replace mentorid = subinstr(mentorid, "STKS", "MTKS",.)
replace mentorid = subinstr(mentorid, "Mtty", "MTTY",.)
replace mentorid = subinstr(mentorid, "MTTYO", "MTTY0",.)
 
replace menteeid = subinstr(menteeid, "mtpp", "mttp",.) 
replace menteeid = subinstr(menteeid, "nscst", "mscst",.) 
replace menteeid = subinstr(menteeid, "stks", "mtks",.) 
replace menteeid = subinstr(menteeid, "myks", "mtks",.) 

replace menteeid = subinstr(menteeid, "mhssr", "msshr",.) 
replace menteeid = subinstr(menteeid, "mssr", "msshr",.) 
replace menteeid = subinstr(menteeid, "mssrh", "msshr",.) 
replace menteeid = subinstr(menteeid, "nsshr", "msshr",.) 


replace menteeid = "mttp0301" if menteeid == "mttp0303" & menteename == "Hemraj Ghalley"
replace menteeid = "mttp4401" if menteeid == "mttp04401"
replace mentorid = "MTTP43" if mentorid == "MTTP303" & mentorname == "Yeshey Tshomo"

replace menteeid = subinstr(menteeid, "mtti", "mttp",.) 
replace menteeid = subinstr(menteeid, "mttv", "mttp",.) 
replace menteeid = subinstr(menteeid, "msct", "mscst",.) 
replace menteeid = subinstr(menteeid, "majne", "msjne",.) 
replace menteeid = subinstr(menteeid, "mkts", "mtks",.) 
replace menteeid = subinstr(menteeid, "mcst", "mscst",.)
replace menteeid = subinstr(menteeid, "mcsct", "mscst",.)

replace menteeid = subinstr(menteeid, "mmty", "mtty",.)

drop if mentorname == "Testing" | mentorid == "1234567" | menteeid == "5mentees"

replace mentorid = "MSCST07" if mentorid == "MCSCT07"
replace mentorid = "MSCST26" if mentorid == "MCST26"
replace mentorid = "MSCNR26" if mentorid == "MSCN08"
replace mentorid = "MSCNR08" if mentorid == "MSCNR26" & menteeid == "mscnr0805"

drop if mentorid == "MSCNR23" & menteeid == "mscnr2105"
drop if menteeid == "dawadem"
replace menteeid = "mscst1105" if menteeid == "mscst1102" & menteename == "Tashi Tenzeen Dhendup"

replace menteeid = subinstr(menteeid, "mscnr20", "mscnr020",.) if mentorid == "MSCNR02"

replace menteeid = "mscnr0403" if menteeid == "mscnr403"

replace menteeid = "mscnr0602" if menteeid == "mscnr0601" & menteename == "Namgay Wangchuk"
replace menteeid = "mscnr0603" if menteeid == "mscnr0601" & menteename == "Sonam Zam"
replace menteeid = "mscnr0604" if menteeid == "mscnr0601" & menteename == "Tandin Bidha"

replace menteeid = "mscnr0701" if menteeid == "mscnr0703" & menteename == "Neelam Gurung"

replace menteeid = "mscnr2001" if menteeid == "mscnr2004" & menteename == "Damcho Zam"

drop if menteeid == "mscnr2305" & mentorid == "MSCNR20"
drop if menteename == "Jigme Wangchuk"	& menteeid == "mscst0904"
replace menteeid = "mscst2004" if menteeid == "mscst2001" & menteename == "Rohit Rai"

drop if strlen(menteename) > 40 
replace menteeid = "mtty1203" if menteeid == "mtty2013"
replace menteeid = "mtty1303" if menteeid == "mtty1304" & menteename == "Phuntsho"
replace menteeid = "mtty1304" if menteeid == "mtty1303" & menteename == "Phuntsho Choden"
replace mentorid = "MTTY01" if mentorid == "MTTY91"

replace menteeid = "msjne0901" if menteename == "Chimi Wangmo" & mentorid == "MSJNE09"
replace menteeid = "msjne0902" if menteename == "Damchey Dema" & mentorid == "MSJNE09"
replace menteeid = "msjne0903" if menteename == "Sonam Cheki" & mentorid == "MSJNE09"
replace menteeid = "msjne0904" if menteename == "Tshering Yangden" & mentorid == "MSJNE09"
replace menteeid = "msjne0905" if menteename == "Yeshi Rangdol" & mentorid == "MSJNE09"

replace menteeid = "msjne1001" if menteename == "Dawa Phuntsho" & mentorid == "MSJNE10"
replace menteeid = "msjne1002" if menteename == "Dendup Phuntsho" & mentorid == "MSJNE10"
replace menteeid = "msjne1003" if menteename == "Jampel Lhamo" & mentorid == "MSJNE10"
replace menteeid = "msjne1004" if menteename == "Kinley Phuntsho" & mentorid == "MSJNE10"
replace menteeid = "msjne1005" if menteename == "Sonam Tobgay" & mentorid == "MSJNE10"

cap drop grade
g grade = 12 if substr(menteeid, 1, 2)== "mt"
replace grade = 10 if substr(menteeid, 1, 2)== "ms"
duplicates drop menteeid thenumberofthemeeting, force

* number of official meetings
bys menteeid: egen num_meeting = max(thenumberofthemeeting)
lab var num_meeting "number of official meetings" 
        

* communication besides official meetings
tab howoftendidyouandyourmenteecommu
tab howoftendidyouandyourmenteecommu if grade==10
tab howoftendidyouandyourmenteecommu if grade==12

gen comm = inlist(howoftendidyouandyourmenteecommu, 2, 3, 4)
bys menteeid: egen communication = mean(comm)
bys menteeid: egen communication_m = max(comm)

lab var communication "share of meetings with at least one communication outside of the meeting"

lab var communication_m "having at least one communication outside of the meeting"

* meeting in minutes 
gen num_minutes = howlongwasthismeetinginminutes

forval i = 1/4 {
	gen m`i'_minutes = num_minutes if thenumberofthemeeting == `i'
	bys menteeid: egen meeting_minutes`i' = max(m`i'_minutes)
	lab var meeting_minutes`i' "length of the meeting in minutes"
}

* time share on each topic during the official meetings and progress for each meeting
destring studentlifeintvetstemhowmuchtime, replace force
destring othersspecifyintheboxbelowhowmuc, replace force
destring howtvetstemstudentsarerecognized, replace force

ren importanceoftvetstemhowmuchtimed share_1
ren studentlifeintvetstemhowmuchtime share_2
ren coursesstudyoptionshowmuchtimedi share_3
ren careerpathhowmuchtimedidyouspend share_4
ren labormarketinformationandjobpros share_5
ren entryrequirementshowmuchtimedidy share_6
ren applicationprocesshowmuchtimedid share_7
ren howtvetstemstudentsarerecognized share_8
ren othersspecifyintheboxbelowhowmuc share_9

ren howmuchtimedidyouspendoneachofth others_sharing 
sort grade others_sharing

export excel others_sharing grade using "$mentor_report\others_text.xls", replace

ren identifyingcareerissuesonascaleo goal_1 
ren understandingtheimportanceofthep goal_2 
ren understandingthecoursesassociate goal_3 
ren understandingthelifeofatypicalst goal_4 
ren understandingthecareerpathofgrad goal_5 
ren understandingtheentryrequirement goal_6 

preserve 
collapse (mean) share_* goal_*, by(thenumberofthemeeting grade)

egen rowsum = rowtotal(share_1-share_9)

forval i=1/9 {
	replace share_`i' = share_`i'/rowsum*100
}

drop if thenumberofthemeeting == . 
drop rowsum* 

reshape long share_ goal_, i(thenumberofthemeeting grade) j(topic) string
ren share_ share
ren goal_ goal 
lab def share 1 "Importance of STEM/TVET" 2 "Student Life Experience" 3 "Courses and Study Options" 4 "Career Path" 5 "Labor Market Information" 6 "Entry Requirement" 7 "Application Process" 8 "General Perception of TVET/STEM" 9 "Others"
lab def goal 1 "Identifying Career Issues" 2 "Understanding Importance of STEM/TVET" 3 "Understanding Course Options" 4 "Understanding Student Life" 5 "Understanding Career Paths" 6 "Understanding Entry Requirement"
lab val share share
lab val goal goal
save "$clean/mentor_report_discussion.dta", replace 
restore 


* date of the first meeting and last meeting
cap drop date date_min date_max interval
replace dateofthemeeting = subinstr(dateofthemeeting, "2012", "2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "2020", "2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "12/04/2004", "11/04/2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "01/03/2005", "10/30/2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "04/12/2005", "11/04/2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "09/29/2004", "10/29/2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "04/03/2004", "11/02/2021",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "06/11", "11/06",.)
replace dateofthemeeting = subinstr(dateofthemeeting, "01/11", "11/01",.)
replace dateofthemeeting = "10/31/2021" if mentorid == "MSGCI42" & thenumberofthemeeting == 1
replace dateofthemeeting = "11/10/2021" if mentorid == "MSCST05" & thenumberofthemeeting == 3
replace dateofthemeeting = "11/09/2021" if mentorid == "MSGCI42" & thenumberofthemeeting == 3
replace dateofthemeeting = "11/10/2021" if menteeid == "msjne2703" & thenumberofthemeeting == 2
replace dateofthemeeting = "11/08/2021" if inlist(menteeid, "mttp4501", "mttp4503", "mttp4504", "mttp4505") & thenumberofthemeeting == 2
replace dateofthemeeting = "11/01/2021" if menteeid == "mttp4505" & thenumberofthemeeting == 1

gen date = date(dateofthemeeting, "MDY")
sort menteeid date
bys menteeid: egen date_min = min(date)
bys menteeid: egen date_max = max(date)
gen interval = date_max - date_min
lab var interval "time interval of all meetings"

* quality of the meeting 
bys menteeid: egen quality_m = total(inlist(howdoyouthinkaboutthetimeyouspen, 3, 4))
gen qual_meeting = quality_m/num_meeting
lab var qual_meeting "share of sufficient meetings"

* share of online meetings 
bys menteeid: egen online_m = total((wasthismeetinginpersonoronline==2))
gen num_online = online_m/num_meeting 
lab var num_online "share of online meetings"



duplicates drop menteeid, force 
keep menteeid menteename num_meeting meeting_minutes* qual_meeting num_online grade interval communication communication_m

tab num_meeting if grade==10
tab num_meeting if grade==12
dis 455/700

su interval if num_meeting==4, de

gen str10 menteeid_new = menteeid
drop menteeid 
rename menteeid_new menteeid 

save "$clean/mentor_report.dta", replace